﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using V5_DBUtility;
using V5_Model;
using System.Data.OleDb;
namespace V5_DAL.SqlServer {
    public class DALUser {

        #region  BaseMethod
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId() {
            return DbHelperSQL.GetMaxID("ID", "K_User");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from K_User");
            strSql.Append(" where ID=" + ID);
            return DbHelperSQL.Exists(strSql.ToString());
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(ModelUser model) {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.ID != null) {
                strSql1.Append("ID,");
                strSql2.Append("" + model.ID + ",");
            }
            if (model.UserName != null) {
                strSql1.Append("UserName,");
                strSql2.Append("'" + model.UserName + "',");
            }
            if (model.UserPassword != null) {
                strSql1.Append("UserPassword,");
                strSql2.Append("'" + model.UserPassword + "',");
            }
            if (model.RegDate != null) {
                strSql1.Append("RegDate,");
                strSql2.Append("'" + model.RegDate + "',");
            }
            if (model.UserScore != null) {
                strSql1.Append("UserScore,");
                strSql2.Append("" + model.UserScore + ",");
            }
            if (model.ScoreUsed != null) {
                strSql1.Append("ScoreUsed,");
                strSql2.Append("" + model.ScoreUsed + ",");
            }
            if (model.UserEmail != null) {
                strSql1.Append("UserEmail,");
                strSql2.Append("'" + model.UserEmail + "',");
            }
            if (model.UserSex != null) {
                strSql1.Append("UserSex,");
                strSql2.Append("" + model.UserSex + ",");
            }
            if (model.LoginDate != null) {
                strSql1.Append("LoginDate,");
                strSql2.Append("'" + model.LoginDate + "',");
            }
            if (model.LoginTimes != null) {
                strSql1.Append("LoginTimes,");
                strSql2.Append("" + model.LoginTimes + ",");
            }
            if (model.IdentityCard != null) {
                strSql1.Append("IdentityCard,");
                strSql2.Append("'" + model.IdentityCard + "',");
            }
            if (model.PostCode != null) {
                strSql1.Append("PostCode,");
                strSql2.Append("" + model.PostCode + ",");
            }
            if (model.AreaID != null) {
                strSql1.Append("AreaID,");
                strSql2.Append("" + model.AreaID + ",");
            }
            if (model.Address3 != null) {
                strSql1.Append("Address3,");
                strSql2.Append("'" + model.Address3 + "',");
            }
            if (model.Address2 != null) {
                strSql1.Append("Address2,");
                strSql2.Append("'" + model.Address2 + "',");
            }
            if (model.Address != null) {
                strSql1.Append("Address,");
                strSql2.Append("'" + model.Address + "',");
            }
            if (model.IsLock != null) {
                strSql1.Append("IsLock,");
                strSql2.Append("" + model.IsLock + ",");
            }
            if (model.GroupID != null) {
                strSql1.Append("GroupID,");
                strSql2.Append("" + model.GroupID + ",");
            }
            if (model.Question != null) {
                strSql1.Append("Question,");
                strSql2.Append("'" + model.Question + "',");
            }
            if (model.Answer != null) {
                strSql1.Append("Answer,");
                strSql2.Append("'" + model.Answer + "',");
            }
            if (model.LoginIP != null) {
                strSql1.Append("LoginIP,");
                strSql2.Append("'" + model.LoginIP + "',");
            }
            if (model.FrozenDeposit != null) {
                strSql1.Append("FrozenDeposit,");
                strSql2.Append("" + model.FrozenDeposit + ",");
            }
            if (model.FrozenScore != null) {
                strSql1.Append("FrozenScore,");
                strSql2.Append("" + model.FrozenScore + ",");
            }
            if (model.BirthDay != null) {
                strSql1.Append("BirthDay,");
                strSql2.Append("'" + model.BirthDay + "',");
            }
            if (model.QQ != null) {
                strSql1.Append("QQ,");
                strSql2.Append("'" + model.QQ + "',");
            }
            if (model.Telephone != null) {
                strSql1.Append("Telephone,");
                strSql2.Append("'" + model.Telephone + "',");
            }
            if (model.Phone != null) {
                strSql1.Append("Phone,");
                strSql2.Append("'" + model.Phone + "',");
            }
            if (model.TrueName != null) {
                strSql1.Append("TrueName,");
                strSql2.Append("'" + model.TrueName + "',");
            }
            if (model.CommendUser != null) {
                strSql1.Append("CommendUser,");
                strSql2.Append("'" + model.CommendUser + "',");
            }
            if (model.NewPassWord != null) {
                strSql1.Append("NewPassWord,");
                strSql2.Append("'" + model.NewPassWord + "',");
            }
            if (model.GUID != null) {
                strSql1.Append("GUID,");
                strSql2.Append("'" + model.GUID + "',");
            }
            if (model.Readme != null) {
                strSql1.Append("Readme,");
                strSql2.Append("'" + model.Readme + "',");
            }
            strSql.Append("insert into K_User(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(ModelUser model) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update K_User set ");
            if (model.UserName != null) {
                strSql.Append("UserName='" + model.UserName + "',");
            }
            if (model.UserPassword != null) {
                strSql.Append("UserPassword='" + model.UserPassword + "',");
            }
            if (model.RegDate != null) {
                strSql.Append("RegDate='" + model.RegDate + "',");
            }
            if (model.UserScore != null) {
                strSql.Append("UserScore=" + model.UserScore + ",");
            }
            if (model.ScoreUsed != null) {
                strSql.Append("ScoreUsed=" + model.ScoreUsed + ",");
            }
            if (model.UserEmail != null) {
                strSql.Append("UserEmail='" + model.UserEmail + "',");
            }
            if (model.UserSex != null) {
                strSql.Append("UserSex=" + model.UserSex + ",");
            }
            if (model.LoginDate != null) {
                strSql.Append("LoginDate='" + model.LoginDate + "',");
            }
            if (model.LoginTimes != null) {
                strSql.Append("LoginTimes=" + model.LoginTimes + ",");
            }
            if (model.IdentityCard != null) {
                strSql.Append("IdentityCard='" + model.IdentityCard + "',");
            }
            if (model.PostCode != null) {
                strSql.Append("PostCode=" + model.PostCode + ",");
            }
            if (model.AreaID != null) {
                strSql.Append("AreaID=" + model.AreaID + ",");
            }
            if (model.Address3 != null) {
                strSql.Append("Address3='" + model.Address3 + "',");
            }
            if (model.Address2 != null) {
                strSql.Append("Address2='" + model.Address2 + "',");
            }
            if (model.Address != null) {
                strSql.Append("Address='" + model.Address + "',");
            }
            if (model.IsLock != null) {
                strSql.Append("IsLock=" + model.IsLock + ",");
            }
            if (model.GroupID != null) {
                strSql.Append("GroupID=" + model.GroupID + ",");
            }
            if (model.Question != null) {
                strSql.Append("Question='" + model.Question + "',");
            }
            if (model.Answer != null) {
                strSql.Append("Answer='" + model.Answer + "',");
            }
            if (model.LoginIP != null) {
                strSql.Append("LoginIP='" + model.LoginIP + "',");
            }
            if (model.FrozenDeposit != null) {
                strSql.Append("FrozenDeposit=" + model.FrozenDeposit + ",");
            }
            if (model.FrozenScore != null) {
                strSql.Append("FrozenScore=" + model.FrozenScore + ",");
            }
            if (model.BirthDay != null) {
                strSql.Append("BirthDay='" + model.BirthDay + "',");
            }
            if (model.QQ != null) {
                strSql.Append("QQ='" + model.QQ + "',");
            }
            if (model.Telephone != null) {
                strSql.Append("Telephone='" + model.Telephone + "',");
            }
            if (model.Phone != null) {
                strSql.Append("Phone='" + model.Phone + "',");
            }
            if (model.TrueName != null) {
                strSql.Append("TrueName='" + model.TrueName + "',");
            }
            if (model.CommendUser != null) {
                strSql.Append("CommendUser='" + model.CommendUser + "',");
            }
            if (model.NewPassWord != null) {
                strSql.Append("NewPassWord='" + model.NewPassWord + "',");
            }
            if (model.GUID != null) {
                strSql.Append("GUID='" + model.GUID + "',");
            }
            if (model.Readme != null) {
                strSql.Append("Readme='" + model.Readme + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where ID=" + model.ID + " ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from K_User ");
            strSql.Append(" where ID=" + ID);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ModelUser GetModel(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from K_User ");
            strSql.Append(" where ID=" + ID);
            ModelUser model = new ModelUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "") {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserName"] != null && ds.Tables[0].Rows[0]["UserName"].ToString() != "") {
                    model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UserPassword"] != null && ds.Tables[0].Rows[0]["UserPassword"].ToString() != "") {
                    model.UserPassword = ds.Tables[0].Rows[0]["UserPassword"].ToString();
                }
                if (ds.Tables[0].Rows[0]["RegDate"] != null && ds.Tables[0].Rows[0]["RegDate"].ToString() != "") {
                    model.RegDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["RegDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserScore"] != null && ds.Tables[0].Rows[0]["UserScore"].ToString() != "") {
                    model.UserScore = int.Parse(ds.Tables[0].Rows[0]["UserScore"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ScoreUsed"] != null && ds.Tables[0].Rows[0]["ScoreUsed"].ToString() != "") {
                    model.ScoreUsed = int.Parse(ds.Tables[0].Rows[0]["ScoreUsed"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserEmail"] != null && ds.Tables[0].Rows[0]["UserEmail"].ToString() != "") {
                    model.UserEmail = ds.Tables[0].Rows[0]["UserEmail"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UserSex"] != null && ds.Tables[0].Rows[0]["UserSex"].ToString() != "") {
                    model.UserSex = int.Parse(ds.Tables[0].Rows[0]["UserSex"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LoginDate"] != null && ds.Tables[0].Rows[0]["LoginDate"].ToString() != "") {
                    model.LoginDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["LoginDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LoginTimes"] != null && ds.Tables[0].Rows[0]["LoginTimes"].ToString() != "") {
                    model.LoginTimes = int.Parse(ds.Tables[0].Rows[0]["LoginTimes"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IdentityCard"] != null && ds.Tables[0].Rows[0]["IdentityCard"].ToString() != "") {
                    model.IdentityCard = ds.Tables[0].Rows[0]["IdentityCard"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PostCode"] != null && ds.Tables[0].Rows[0]["PostCode"].ToString() != "") {
                    model.PostCode = int.Parse(ds.Tables[0].Rows[0]["PostCode"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AreaID"] != null && ds.Tables[0].Rows[0]["AreaID"].ToString() != "") {
                    model.AreaID = int.Parse(ds.Tables[0].Rows[0]["AreaID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Address3"] != null && ds.Tables[0].Rows[0]["Address3"].ToString() != "") {
                    model.Address3 = ds.Tables[0].Rows[0]["Address3"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Address2"] != null && ds.Tables[0].Rows[0]["Address2"].ToString() != "") {
                    model.Address2 = ds.Tables[0].Rows[0]["Address2"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Address"] != null && ds.Tables[0].Rows[0]["Address"].ToString() != "") {
                    model.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsLock"] != null && ds.Tables[0].Rows[0]["IsLock"].ToString() != "") {
                    model.IsLock = int.Parse(ds.Tables[0].Rows[0]["IsLock"].ToString());
                }
                if (ds.Tables[0].Rows[0]["GroupID"] != null && ds.Tables[0].Rows[0]["GroupID"].ToString() != "") {
                    model.GroupID = int.Parse(ds.Tables[0].Rows[0]["GroupID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Question"] != null && ds.Tables[0].Rows[0]["Question"].ToString() != "") {
                    model.Question = ds.Tables[0].Rows[0]["Question"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Answer"] != null && ds.Tables[0].Rows[0]["Answer"].ToString() != "") {
                    model.Answer = ds.Tables[0].Rows[0]["Answer"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LoginIP"] != null && ds.Tables[0].Rows[0]["LoginIP"].ToString() != "") {
                    model.LoginIP = ds.Tables[0].Rows[0]["LoginIP"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FrozenDeposit"] != null && ds.Tables[0].Rows[0]["FrozenDeposit"].ToString() != "") {
                    model.FrozenDeposit = int.Parse(ds.Tables[0].Rows[0]["FrozenDeposit"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FrozenScore"] != null && ds.Tables[0].Rows[0]["FrozenScore"].ToString() != "") {
                    model.FrozenScore = int.Parse(ds.Tables[0].Rows[0]["FrozenScore"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BirthDay"] != null && ds.Tables[0].Rows[0]["BirthDay"].ToString() != "") {
                    model.BirthDay = ds.Tables[0].Rows[0]["BirthDay"].ToString();
                }
                if (ds.Tables[0].Rows[0]["QQ"] != null && ds.Tables[0].Rows[0]["QQ"].ToString() != "") {
                    model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Telephone"] != null && ds.Tables[0].Rows[0]["Telephone"].ToString() != "") {
                    model.Telephone = ds.Tables[0].Rows[0]["Telephone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "") {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TrueName"] != null && ds.Tables[0].Rows[0]["TrueName"].ToString() != "") {
                    model.TrueName = ds.Tables[0].Rows[0]["TrueName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CommendUser"] != null && ds.Tables[0].Rows[0]["CommendUser"].ToString() != "") {
                    model.CommendUser = ds.Tables[0].Rows[0]["CommendUser"].ToString();
                }
                if (ds.Tables[0].Rows[0]["NewPassWord"] != null && ds.Tables[0].Rows[0]["NewPassWord"].ToString() != "") {
                    model.NewPassWord = ds.Tables[0].Rows[0]["NewPassWord"].ToString();
                }
                if (ds.Tables[0].Rows[0]["GUID"] != null && ds.Tables[0].Rows[0]["GUID"].ToString() != "") {
                    model.GUID = ds.Tables[0].Rows[0]["GUID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Readme"] != null && ds.Tables[0].Rows[0]["Readme"].ToString() != "") {
                    model.Readme = ds.Tables[0].Rows[0]["Readme"].ToString();
                }
                return model;
            }
            else {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM K_User ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where  1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  Method

        #region K_User开始
        public bool DeleteList(string IDlist) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from K_User ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder) {
            return this.GetList("*", Top, strWhere, filedOrder);
        }

        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            return this.GetList("*", PageSize, PageIndex, strWhere, strOrder, ref RecordCount);
        }

        public List<ModelUser> GetModelList(string strWhere) {
            DataSet ds = this.GetList(strWhere);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                return DBCommonHelper.FillModel<ModelUser>(ds);
            }
            return null;
        }

        public List<ModelUser> GetModelList(int Top, string strWhere, string filedOrder) {
            DataSet ds = this.GetList(Top, strWhere, filedOrder);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                return DBCommonHelper.FillModel<ModelUser>(ds);
            }
            return null;
        }

        public List<ModelUser> GetModelList(int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            DataSet ds = this.GetList(PageSize, PageIndex, strWhere, strOrder, ref RecordCount);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                return DBCommonHelper.FillModel<ModelUser>(ds);
            }
            return null;
        }

        public DataSet GetList(string strFiled, string strWhere) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select " + strFiled + " ");
            strSql.Append(" FROM K_User ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList(string strFiled, int Top, string strWhere, string filedOrder) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0) {
                strSql.Append(" Top " + Top.ToString());
            }
            strSql.Append(" " + strFiled + " ");
            strSql.Append(" FROM K_User ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where 1=1 " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList(string strFiled, int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            StringBuilder strSqlCount = new StringBuilder();
            strSqlCount.Append("Select Count(*) as ID From K_User ");
            if (strWhere.Trim() != "") {
                strSqlCount.Append(" where 1=1 " + strWhere);
            }
            RecordCount = Convert.ToInt32("0" + DbHelperSQL.GetSingle(strSqlCount.ToString()));
            if (PageIndex <= 1) {
                PageIndex = 1;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Top " + PageSize + " " + strFiled + " from K_User where 1=1 ");
            if (strWhere.Trim() != "") {
                strSql.Append(" " + strWhere);
            }
            if (PageIndex > 1) {
                strSql.Append(" And ID Not In (Select Top " + ((PageIndex - 1) * PageSize) + " ID From K_User Where 1=1 ");
                if (strWhere.Trim() != "") {
                    strSql.Append(" " + strWhere);
                }
                if (!string.IsNullOrEmpty(strOrder)) {
                    strSql.Append(" Order by " + strOrder);
                }
                strSql.Append(")");
            }
            if (!string.IsNullOrEmpty(strOrder)) {
                strSql.Append(" Order by " + strOrder);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion K_User结束




    }
}
